from flask import Flask, render_template, request, jsonify
import sqlite3
import os

app = Flask(__name__)

def get_db_connection():
    conn = sqlite3.connect('../steam_games.db')
    conn.row_factory = sqlite3.Row
    return conn

@app.route('/index')
def index():
    conn = get_db_connection()
    # 获取最新上架的游戏
    latest_games = conn.execute('''
        SELECT * FROM games 
        WHERE 上架时间 != 'N/A' 
        ORDER BY 上架时间 DESC 
        LIMIT 12
    ''').fetchall()
    
    # 获取好评游戏
    top_rated = conn.execute('''
        SELECT * FROM games 
        WHERE 评价等级 = '好评' 
        ORDER BY RANDOM() 
        LIMIT 12
    ''').fetchall()
    
    # 获取折扣游戏
    discounted = conn.execute('''
        SELECT * FROM games 
        WHERE 折扣 != 'N/A' 
        ORDER BY RANDOM() 
        LIMIT 12
    ''').fetchall()
    
    conn.close()
    return render_template('index.html', 
                         latest_games=latest_games,
                         top_rated=top_rated,
                         discounted=discounted)

@app.route('/')
def all_games():
    limit = request.args.get('limit', 100) # 默认显示100条
    conn = get_db_connection()
    total_count = conn.execute('SELECT COUNT(*) FROM games').fetchone()[0]
    
    if limit == 'all':
        games = conn.execute('SELECT * FROM games').fetchall()
    else:
        try:
            limit_int = int(limit)
            games = conn.execute('SELECT * FROM games LIMIT ?', (limit_int,)).fetchall()
        except ValueError:
            # 如果limit不是'all'且无法转换为整数，则使用默认值100
            games = conn.execute('SELECT * FROM games LIMIT 100').fetchall()

    conn.close()
    return render_template('all_games.html', games=games, total_count=total_count, current_limit=limit)

@app.route('/search')
def search():
    query = request.args.get('q', '')
    conn = get_db_connection()
    games = conn.execute('''
        SELECT * FROM games 
        WHERE 游戏名称 LIKE ? 
        OR 评价总结 LIKE ?
        LIMIT 50
    ''', (f'%{query}%', f'%{query}%')).fetchall()
    conn.close()
    return render_template('search.html', games=games, query=query)

@app.route('/game/<appid>')
def game_detail(appid):
    conn = get_db_connection()
    game = conn.execute('SELECT * FROM games WHERE appid = ?', (appid,)).fetchone()
    conn.close()
    if game is None:
        return "Game not found", 404
    return render_template('game_detail.html', game=game)

@app.route('/platform/<platform>')
def platform_games(platform):
    conn = get_db_connection()
    games = conn.execute('''
        SELECT * FROM games 
        WHERE 游戏平台 LIKE ? 
        LIMIT 1000
    ''', (f'%{platform}%',)).fetchall()
    conn.close()
    return render_template('platform.html', games=games, platform=platform)


@app.route('/api/games_by_year_stats')
def api_games_by_year_stats():
    # 获取数据库连接
    conn = get_db_connection()

@app.route('/Sys_game')
def game_sys():
    conn = get_db_connection()
    # 获取系统兼容性统计数据
    sys_stats_row = conn.execute('''
        SELECT 
            SUM(CASE WHEN 游戏平台 LIKE '%Windows%' THEN 1 ELSE 0 END) as windows,
            SUM(CASE WHEN 游戏平台 LIKE '%Mac%' THEN 1 ELSE 0 END) as mac,
            SUM(CASE WHEN 游戏平台 LIKE '%Linux%' THEN 1 ELSE 0 END) as linux
        FROM games
    ''').fetchone()
    
    # 将 Row 对象转换为字典
    sys_stats = dict(sys_stats_row) if sys_stats_row else {}
    
    conn.close()
    return render_template('sys_game.html', sys_stats=sys_stats)

@app.route('/price_game')
def price_game():
    conn = get_db_connection()
    # 获取折扣游戏
    discounted_rows = conn.execute('''
            SELECT * FROM games 
            WHERE 折扣 != 'N/A' 
            ORDER BY RANDOM() 
            LIMIT 1000
        ''').fetchall()

    conn.close()
    
    # 将 Row 对象列表转换为字典列表
    discounted_list = [dict(row) for row in discounted_rows]
    
    return render_template('price_game.html',
                           price_stats=discounted_list)

if __name__ == '__main__':
    app.run(debug=True)
